package com.faf.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.faf.bean.Access;
import com.faf.bean.Comment;
import com.faf.bean.Food;
import com.faf.dao.AccessDao;
import com.faf.dao.CommentDao;

public class AccessDaoImpl extends BaseDaoImpl implements AccessDao {

	public AccessDaoImpl() {

	}

	public AccessDaoImpl(Connection con) {
		super(con);
	}
	
	@Override
	public void newAccess(int storeId, int foodId, int userId) throws SQLException {
		// TODO Auto-generated method stub
		Access access = new Access(storeId, foodId);
		
		String sql_select = "select max(access_timestamp) from access where access_user = ?";
		this.executeQuery(sql_select, userId);
		if(rs.next()) {
			String res = null == rs.getString(1) ? "0" : rs.getString(1); 
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Long dataInSql = Long.parseLong(sdf.format(new Date(Long.parseLong(res))));
			Long dataInDat = Long.parseLong(sdf.format(new Date(access.timestamp())));
			if(dataInDat - dataInSql < 1)
				return;
		}
		
		String sql_insert = "insert into access (access_timestamp, access_store, access_food, access_user) values (?, ?, ?, ?)";
		this.executeUpdate(sql_insert, access.timestamp(), access.storeId(), access.foodId(), userId);	
	}
	
	@Override
	public long getAccessByStoreId(int storeId) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_store = ?";
		this.executeQuery(sql, storeId);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByStoreId_TimeStamp_After(int storeId, long tms) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(1) from access where access_store = ? and access_timestamp > ?";
		this.executeQuery(sql, storeId, tms);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByStoreId_TimeStamp_Before(int storeId, long tms) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_store = ? and access_timestamp < ?";
		this.executeQuery(sql, storeId, tms);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByStoreId_TimeStamp_Between(int storeId, long from, long to) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_store = ? and access_timestamp > ? and access_timestamp < to";
		this.executeQuery(sql, storeId, from, to);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByStoreNumber(String storeNumber) throws SQLException {
		// TODO Auto-generated method stub
		StoreDaoImpl storeDao = new StoreDaoImpl();
		int storeId = storeDao.getStoreByNumber(storeNumber).getStoreId();
		return this.getAccessByStoreId(storeId);
	}

	@Override
	public long getAccessByStoreNumber_TimeStamp_After(String storeNumber, long tms) throws SQLException {
		// TODO Auto-generated method stub
		StoreDaoImpl storeDao = new StoreDaoImpl();
		int storeId = storeDao.getStoreByNumber(storeNumber).getStoreId();
		return this.getAccessByStoreId_TimeStamp_After(storeId, tms);
	}

	@Override
	public long getAccessByStoreNumber_TimeStamp_Before(String storeNumber, long tms) throws SQLException {
		// TODO Auto-generated method stub
		StoreDaoImpl storeDao = new StoreDaoImpl();
		int storeId = storeDao.getStoreByNumber(storeNumber).getStoreId();
		return this.getAccessByStoreId_TimeStamp_Before(storeId, tms);
	}

	@Override
	public long getAccessByStoreNumber_TimeStamp_Between(String storeNumber, long from, long to) throws SQLException {
		// TODO Auto-generated method stub
		StoreDaoImpl storeDao = new StoreDaoImpl();
		int storeId = storeDao.getStoreByNumber(storeNumber).getStoreId();
		return this.getAccessByStoreId_TimeStamp_Between(storeId, from, to);
	}

	@Override
	public long getAccessByFoodId(int foodId) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_food = ?";
		this.executeQuery(sql, foodId);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByFoodId_TimeStamp_After(int foodId, long tms) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_food = ? and access_timestamp > ?";
		this.executeQuery(sql, foodId, tms);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByFoodId_TimeStamp_Before(int foodId, long tms) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_food = ? and access_timestamp < ?";
		this.executeQuery(sql, foodId, tms);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public long getAccessByFoodId_TimeStamp_Between(int foodId, long from, long to) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select COUNT(*) from access where access_food = ? and access_timestamp > ? and access_timestamp < ?";
		this.executeQuery(sql, foodId, from, to);
		long pv = 0;
		if (rs.next()) {
			pv = rs.getLong(1);
		}
		close();
		super.con = null;
		return pv;
	}

	@Override
	public ArrayList<Food> getFoodByFoodCount(String foodType, int currentPage, int pageTotal) throws SQLException {
		// TODO Auto-generated method stub
		System.out.println("哈哈哈");
		System.out.println(foodType);
		String sql = "select foodId,storeId,foodName,foodPrice,foodType,foodDescribe,foodPhoto,count(food.foodId) AS foodaccessTotal from food,access where food.foodId = access.access_food and food.foodType = ? GROUP BY access.access_food order by foodaccessTotal DESC, food.foodId limit ?,?";

		executeQuery(sql, foodType, (currentPage - 1) * pageTotal, pageTotal);
		Food food = null;
		ArrayList<Food> foods = new ArrayList<Food>();
		while (rs.next()) {
			food = new Food();
			food.setFoodId(Integer.parseInt(rs.getString("foodId")));
			food.setStoreId(Integer.parseInt(rs.getString("storeId")));
			food.setFoodName(rs.getString("foodName"));
			food.setFoodPrice(rs.getDouble("foodPrice"));
			food.setFoodType(foodType);
			food.setFoodDescribe(rs.getString("foodDescribe"));
			food.setFoodPhoto(rs.getString("foodPhoto"));
			foods.add(food);
		}
		return foods;
	}

	@Override
	public int getAllFoodRecordByFoodType(String foodType) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select count(1) from (select distinct foodId,storeId,foodName,foodPrice,foodType,foodDescribe,foodPhoto from food,access where food.foodId = access.access_food and food.foodType = ? ) as a";
		executeQuery(sql, foodType);
		int result = 0;
		if (rs != null) {
			if (rs.next()) {
				result = rs.getInt(1);
				System.out.println(result);
			}
		}
		return result;
	}

	@Override
	public ArrayList<Integer> getFoodTopList() throws SQLException {
		// TODO Auto-generated method stub
		ArrayList<Integer> toplist = new ArrayList<Integer>();
		
		String sql = "select foodId,storeId,foodName,foodPrice,foodType,foodDescribe,foodPhoto,count(food.foodId) AS foodaccessTotal from food,access where food.foodId = access.access_food GROUP BY access.access_food order by foodaccessTotal DESC, food.foodId;";
		this.executeQuery(sql);
		
		while(rs.next()) {
			toplist.add(rs.getInt(1));
		}
		
		return toplist;
	}
}
